<?php
/**
 * author zhaotingquan
 * time 2019-09-06 13:26 星期五
 * desc 测试Demo
 */

namespace App\Http\Controllers\Index;


use App\Http\Controllers\Controller;
use GuzzleHttp\Client;
use Illuminate\Http\Request;
use PhpOffice\PhpSpreadsheet\IOFactory;

class IndexController extends Controller
{
    /**
     * 实例
     * @param Request $request
     * @return \Illuminate\Contracts\View\Factory|\Illuminate\View\View
     */
    public function index(Request $request)
    {
        ini_set('max_execution_time', 6000);
        $file_path = "E:\桌面文件夹\进度.xlsx";
        $spreadsheet = IOFactory::load($file_path);
        $spreadsheet->getSheetCount();//获取sheet页总数
        $sheet = $spreadsheet->getSheet(0);//sheet
        $merges = $sheet->getMergeCells();//获取合并数据
        $title = $sheet->getTitle();//获取sheet标题
        $freeze = $sheet->getFreezePane();//获取冻结窗体
        $rowCount = $sheet->getHighestRow();//总行数
        $colStrCount = $sheet->getHighestColumn();//最大列数字母
        $colCount = self::getNum("A", $colStrCount) + 1;//总列数，方法计算出来的值是从0开始所以需要+1
        $result = array();
        $result['name'] = $title;
        $result['merges'] = array_values($merges);
        if ($freeze != null) $result['freeze'] = $freeze;
        $styleFlag = 0;//样式数组的flag
        $verifyMergeCells = array();
        foreach ($sheet->getRowIterator() as $row) {
            $rowIndex = $row->getRowIndex();
            $rowHeight = $row->getWorksheet()->getRowDimension($rowIndex)->getRowHeight();//行高
            foreach ($row->getCellIterator() as $cell) {
                $bgcolor = $cell->getStyle()->getFill()->getStartColor()->getRGB();//获取背景色
                $textwrap = $cell->getStyle()->getAlignment()->getWrapText();//获取自动换行属性
                $color = $cell->getStyle()->getFont()->getColor()->getRGB();//获取字体颜色
                $fontSize = $cell->getStyle()->getFont()->getSize();//获取字号
                $bold = $cell->getStyle()->getFont()->getBold();//是否加粗
                $italic = $cell->getStyle()->getFont()->getItalic();//是否斜体
                $underline = $cell->getStyle()->getFont()->getUnderline();//是否下划线
                $strike = $cell->getStyle()->getFont()->getStrikethrough();//是否删除线
                $fontName = $cell->getStyle()->getFont()->getName();//字体样式
                $leftColor = $cell->getStyle()->getBorders()->getLeft()->getColor()->getRGB();//左侧边框颜色
                $leftBorderStyle = $cell->getStyle()->getBorders()->getLeft()->getBorderStyle();//左侧边框粗细
                $rightColor = $cell->getStyle()->getBorders()->getRight()->getColor()->getRGB();//右侧边框颜色
                $rightBorderStyle = $cell->getStyle()->getBorders()->getRight()->getBorderStyle();//右侧边框粗细
                $topColor = $cell->getStyle()->getBorders()->getTop()->getColor()->getRGB();//上侧边框颜色
                $topBorderStyle = $cell->getStyle()->getBorders()->getTop()->getBorderStyle();//上侧边框粗细
                $bottomColor = $cell->getStyle()->getBorders()->getBottom()->getColor()->getRGB();//下侧边框颜色
                $bottomBorderStyle = $cell->getStyle()->getBorders()->getBottom()->getBorderStyle();//下侧边框粗细
                $align = $cell->getStyle()->getAlignment()->getHorizontal();//水平对齐方式，center,left,right,general
                $valign = $cell->getStyle()->getAlignment()->getVertical();//竖直方向对齐方式，top，center，bottom
                $colWidth = $cell->getWorksheet()->getColumnDimension($cell->getColumn())->getWidth();//列宽
                if ($valign == "center") $valign = 'middle';
                $underline = $underline == "none" ? false : true;
                if ($cell->getMergeRange() && !in_array($cell->getMergeRange(), $verifyMergeCells)) {//筛重
                    //目标A1:B2 =》 A 1 B 2全分开
                    $bridgeStr = explode(":", $cell->getMergeRange());//将A1:A2拆分成A1和A2
                    $front = str_split($bridgeStr[0] ?? "");//将字符串打散为数组
                    $after = str_split($bridgeStr[1] ?? "");
                    $frontCol = $front[0] ?? "";//A  列
                    $afterCol = $after[0] ?? "";//B
                    $frontRow = $front[1] ?? "";//1  行
                    $afterRow = $after[1] ?? "";//2
                    $coordinateRow = self::getNum($frontRow, $afterRow);//行坐标
                    $coordinateCol = self::getNum($frontCol, $afterCol);//列坐标
                    $verifyMergeCells = array_merge($verifyMergeCells, [$cell->getMergeRange()]);
                    $result['rows'][$rowIndex - 1]['cells'][self::getNum('A', $cell->getColumn())]['merge'] = [$coordinateRow, $coordinateCol];
                }
                //-------
                $result['styles'][$styleFlag]['bgcolor'] = '#' . $bgcolor;
                $result['styles'][$styleFlag]['textwrap'] = $textwrap;
                $result['styles'][$styleFlag]['color'] = '#' . $color;
                $result['styles'][$styleFlag]['border']['left'] = [$leftBorderStyle, $leftColor];
                $result['styles'][$styleFlag]['border']['right'] = [$rightBorderStyle, $rightColor];
                $result['styles'][$styleFlag]['border']['top'] = [$topBorderStyle, $topColor];
                $result['styles'][$styleFlag]['border']['bottom'] = [$bottomBorderStyle, $bottomColor];
                if ($fontSize != 11) $result['styles'][$styleFlag]['font']['size'] = $fontSize;//字号
                $result['styles'][$styleFlag]['font']['name'] = $fontName;//字体格式
                $result['styles'][$styleFlag]['font']['bold'] = $bold;//是否加粗
                $result['styles'][$styleFlag]['font']['italic'] = $italic;//斜体字
                $result['styles'][$styleFlag]['underline'] = $underline;//下划线
                $result['styles'][$styleFlag]['strike'] = $strike;//删除线
                if ($align != "general") $result['styles'][$styleFlag]['align'] = $align;//水平对齐方式
                $result['styles'][$styleFlag]['valign'] = $valign;//竖直对齐方式
                $result['cols']['len'] = $colCount;
                $result['rows'][$rowIndex - 1]['cells'][self::getNum('A', $cell->getColumn())]['text'] = $cell->getFormattedValue();
                $result['rows'][$rowIndex - 1]['cells'][self::getNum('A', $cell->getColumn())]['style'] = $styleFlag;
                if ($colWidth != -1) $result['cols'][self::getNum('A', $cell->getColumn())]['width'] = $colWidth * 8;
                $styleFlag++;
            }
            if ($rowHeight != -1) $result['rows'][$rowIndex - 1]['height'] = $rowHeight * 1.5;
            $result['rows']['len'] = $rowCount;
        }
        print_r(json_encode($result));
    }

    //获取两个字母之间的间隔
    function getNum($start, $end)
    {
        $flag = 0;
        for ($i = $start; $i != $end; $i++) {
            $flag++;
        }
        return $flag;
    }

    //备份
    public function indexBak(Request $request)
    {
        ini_set('max_execution_time', 600);
        //$file_path = "E:\桌面文件夹\进度.xlsx";
        $spreadsheet = IOFactory::load($file_path);
        $spreadsheet->getSheetCount();//获取sheet页总数
        $sheet = $spreadsheet->getSheet(0);//sheet
        $merges = $sheet->getMergeCells();//获取合并数据
        $title = $sheet->getTitle();//获取sheet标题
        $freeze = $sheet->getFreezePane();//获取冻结窗体
        $rowCount = $sheet->getHighestRow();//总行数
        $colStrCount = $sheet->getHighestColumn();//最大列数字母
        $colCount = self::getNum("A", $colStrCount) + 1;//总列数，方法计算出来的值是从0开始所以需要+1
        $result = array();
        $result['name'] = $title;
        $result['merges'] = array_values($merges);
        $result['freeze'] = $freeze;
        $styleFlag = 0;//样式数组的flag
        $verifyMergeCells = array();
        foreach ($sheet->getRowIterator() as $row) {
            $rowIndex = $row->getRowIndex();
            $rowHeight = $row->getWorksheet()->getRowDimension($rowIndex)->getRowHeight();//行高
            foreach ($row->getCellIterator() as $cell) {
                $styleObject = $cell->getStyle();
                $fontObject = $styleObject->getFont();
                $bordersObject = $styleObject->getBorders();
                $alignmentObject = $styleObject->getAlignment();
                $bgcolor = $styleObject->getFill()->getStartColor()->getRGB();//获取背景色
                $color = $fontObject->getColor()->getRGB();//获取字体颜色
                $fontSize = $fontObject->getSize();//获取字号
                $bold = $fontObject->getBold();//是否加粗
                $italic = $fontObject->getItalic();//是否斜体
                $underline = $fontObject->getUnderline();//是否下划线
                $strike = $fontObject->getStrikethrough();//是否删除线
                $fontName = $fontObject->getName();//字体样式
                $leftColor = $bordersObject->getLeft()->getColor()->getRGB();//左侧边框颜色
                $leftBorderStyle = $bordersObject->getLeft()->getBorderStyle();//左侧边框粗细
                $rightColor = $bordersObject->getRight()->getColor()->getRGB();//右侧边框颜色
                $rightBorderStyle = $bordersObject->getRight()->getBorderStyle();//右侧边框粗细
                $topColor = $bordersObject->getTop()->getColor()->getRGB();//上侧边框颜色
                $topBorderStyle = $bordersObject->getTop()->getBorderStyle();//上侧边框粗细
                $bottomColor = $bordersObject->getBottom()->getColor()->getRGB();//下侧边框颜色
                $bottomBorderStyle = $bordersObject->getBottom()->getBorderStyle();//下侧边框粗细
                $textwrap = $alignmentObject->getWrapText();//获取自动换行属性
                $align = $alignmentObject->getHorizontal();//水平对齐方式，center,left,right,general
                $valign = $alignmentObject->getVertical();//竖直方向对齐方式，top，center，bottom
                $colWidth = $cell->getWorksheet()->getColumnDimension($cell->getColumn())->getWidth();//列宽
                if ($valign == "center") $valign = 'middle';
                $underline = $underline == "none" ? false : true;
                if ($cell->getMergeRange() && !in_array($cell->getMergeRange(), $verifyMergeCells)) {//筛重
                    //目标A1:B2 =》 A 1 B 2全分开
                    $bridgeStr = explode(":", $cell->getMergeRange());//将A1:A2拆分成A1和A2
                    $front = str_split($bridgeStr[0] ?? "");//将字符串打散为数组
                    $after = str_split($bridgeStr[1] ?? "");
                    $frontCol = $front[0] ?? "";//A  列
                    $afterCol = $after[0] ?? "";//B
                    $frontRow = $front[1] ?? "";//1  行
                    $afterRow = $after[1] ?? "";//2
                    $coordinateRow = self::getNum($frontRow, $afterRow);//行坐标
                    $coordinateCol = self::getNum($frontCol, $afterCol);//列坐标
                    $verifyMergeCells = array_merge($verifyMergeCells, [$cell->getMergeRange()]);
                    $result['rows'][$rowIndex - 1]['cells'][self::getNum('A', $cell->getColumn())]['merge'] = [$coordinateRow, $coordinateCol];
                }
                //-------
                $result['styles'][$styleFlag]['bgcolor'] = '#' . $bgcolor;
                $result['styles'][$styleFlag]['textwrap'] = $textwrap;
                $result['styles'][$styleFlag]['color'] = '#' . $color;
                $result['styles'][$styleFlag]['border']['left'] = [$leftBorderStyle, $leftColor];
                $result['styles'][$styleFlag]['border']['right'] = [$rightBorderStyle, $rightColor];
                $result['styles'][$styleFlag]['border']['top'] = [$topBorderStyle, $topColor];
                $result['styles'][$styleFlag]['border']['bottom'] = [$bottomBorderStyle, $bottomColor];
                $result['styles'][$styleFlag]['font']['size'] = $fontSize;//字号
                $result['styles'][$styleFlag]['font']['name'] = $fontName;//字体格式
                $result['styles'][$styleFlag]['font']['bold'] = $bold;//是否加粗
                $result['styles'][$styleFlag]['font']['italic'] = $italic;//斜体字
                $result['styles'][$styleFlag]['underline'] = $underline;//下划线
                $result['styles'][$styleFlag]['strike'] = $strike;//删除线
                if ($align != "general") $result['styles'][$styleFlag]['align'] = $align;//水平对齐方式
                $result['styles'][$styleFlag]['valign'] = $valign;//竖直对齐方式
                $result['cols']['len'] = $colCount;
                $result['rows'][$rowIndex - 1]['cells'][self::getNum('A', $cell->getColumn())]['text'] = $cell->getFormattedValue();
                $result['rows'][$rowIndex - 1]['cells'][self::getNum('A', $cell->getColumn())]['style'] = $styleFlag;
                if ($colWidth != -1) $result['cols'][self::getNum('A', $cell->getColumn())]['width'] = $colWidth * 10;
                $styleFlag++;
            }
            if ($rowHeight != -1) $result['rows'][$rowIndex - 1]['height'] = $rowHeight;
            $result['rows']['len'] = $rowCount;
            dd($result);
        }
        print_r(json_encode($result));
    }
}
